-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('admin', 'user')),
    avatar VARCHAR(255),
    "isActive" BOOLEAN DEFAULT true,
    "emailVerified" BOOLEAN DEFAULT false,
    "lastLoginAt" TIMESTAMP,
    "lastLoginIp" VARCHAR(45),
    "loginCount" INTEGER DEFAULT 0,
    preferences JSONB,
    "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建爬虫任务表
CREATE TABLE IF NOT EXISTS crawler_tasks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    url TEXT NOT NULL,
    config JSONB NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'completed', 'failed', 'paused')),
    engine VARCHAR(50) DEFAULT 'http',
    schedule VARCHAR(100),
    "isActive" BOOLEAN DEFAULT true,
    priority INTEGER DEFAULT 0,
    "retryCount" INTEGER DEFAULT 0,
    "maxRetries" INTEGER DEFAULT 3,
    "lastRunAt" TIMESTAMP,
    "nextRunAt" TIMESTAMP,
    "completedAt" TIMESTAMP,
    "errorMessage" TEXT,
    stats JSONB,
    "createdBy" UUID,
    "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建爬取数据表
CREATE TABLE IF NOT EXISTS crawled_data (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    "taskId" UUID NOT NULL,
    url TEXT NOT NULL,
    data JSONB NOT NULL,
    hash VARCHAR(64) UNIQUE NOT NULL,
    engine VARCHAR(50),
    "statusCode" INTEGER,
    "responseTime" INTEGER,
    size INTEGER,
    metadata JSONB,
    "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("taskId") REFERENCES crawler_tasks(id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_crawler_tasks_status ON crawler_tasks(status);
CREATE INDEX IF NOT EXISTS idx_crawler_tasks_created_by ON crawler_tasks("createdBy");
CREATE INDEX IF NOT EXISTS idx_crawled_data_task_id_created_at ON crawled_data("taskId", "createdAt");
CREATE INDEX IF NOT EXISTS idx_crawled_data_url ON crawled_data(url);
CREATE INDEX IF NOT EXISTS idx_crawled_data_hash ON crawled_data(hash);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW."updatedAt" = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为表添加更新时间触发器
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_crawler_tasks_updated_at ON crawler_tasks;
CREATE TRIGGER update_crawler_tasks_updated_at 
    BEFORE UPDATE ON crawler_tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_crawled_data_updated_at ON crawled_data;
CREATE TRIGGER update_crawled_data_updated_at 
    BEFORE UPDATE ON crawled_data
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
